﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using MapCenterSystem.DataInfo;
using MapCenterSystem.DefineType;
using System.Data.SqlClient;
using MapCenterSystem.Configuration;
using System.Collections;

namespace MapCenterSystem.DataAccess
{
    public class StreetServices
    {

        public List<StreetInfo> GetListStreet(List<NodeInfo> lstNode)
        {
            List<StreetInfo> lstResult = new List<StreetInfo>();
            for (int i = 0; i < lstNode.Count-1; i++)
            {
               using (SqlConnection sqlConnect = new SqlConnection(GlobalConfig.ConnectionString))
                {
                    SqlCommand command = new SqlCommand();
                    command.CommandText = "sp_StreetSegment_GetNode";
                    command.CommandType = CommandType.StoredProcedure;
                    command.Connection = sqlConnect;
                    command.CommandTimeout = GlobalConfig.TimeOut;
                    command.Parameters.AddWithValue("@start", lstNode[i].Id);
                    command.Parameters.AddWithValue("@end", lstNode[i + 1].Id);
                    try
                    {
                        sqlConnect.Open();
                    }
                    catch
                    {
                        throw new Exception("Can not connect to database. Please check connect");
                    }
                    StreetInfo street = new StreetInfo();
                    street.StartPoint = lstNode[i];
                    street.EndPoint = lstNode[i + 1];
                    SqlDataReader reader = command.ExecuteReader();
                    while (reader.Read())
                    {
                        street.FullName = Convert.ToString(reader["FullName"]);
                        street.ID = Convert.ToInt32(reader["ID"]);
                        street.Direction = Convert.ToInt32(reader["direction"]);
                        if (lstResult.IndexOf(street) < 0)
                            lstResult.Add(street);
                    }
                }
                
            }
            return lstResult;
        }

        private List<SegmentInfo> GetAllSegment(int IDStreet)
        {
            using (SqlConnection sqlConnect = new SqlConnection(GlobalConfig.ConnectionString))
            {
                SqlCommand command = new SqlCommand();
                command.CommandText = "sp_StreetSegment_GetListSegmentOfStreet";
                command.CommandType = CommandType.StoredProcedure;
                command.Connection = sqlConnect;
                command.CommandTimeout = GlobalConfig.TimeOut;
                command.Parameters.AddWithValue("@idStreet", IDStreet);
                try
                {
                    sqlConnect.Open();
                }
                catch
                {
                    throw new Exception("Can not connect to database. Please check connect");
                }
                SqlDataReader reader = command.ExecuteReader();
                List<SegmentInfo> result = new List<SegmentInfo>();
                while (reader.Read())
                {
                    SegmentInfo segment = new SegmentInfo();
                    int endPoint = Convert.ToInt32(reader["endpoint"]);
                    int startPoint = Convert.ToInt32(reader["startpoint"]);
                    NodeService node = new NodeService();
                    segment.IdStreet = Convert.ToInt32(reader["IDStreet"]);
                    segment.StartPoint = node.GetNodeInfo(startPoint);
                    segment.Order = Convert.ToInt32(reader["order"]);
                    segment.EndPoint = node.GetNodeInfo(endPoint);
                    result.Add(segment);
                }
                return result;
            }

        }
        
        public IList<HomeInfo> FindAddress(String keyword)
        {
            using (SqlConnection sqlConnect = new SqlConnection(GlobalConfig.ConnectionString))
            {
                SqlCommand command = new SqlCommand();
                command.CommandText = "sp_StreetDetail_FindAddress";
                command.CommandType = CommandType.StoredProcedure;
                command.Connection = sqlConnect;
                command.CommandTimeout = GlobalConfig.TimeOut;
                command.Parameters.AddWithValue("@address", keyword);
                
                try
                {
                    sqlConnect.Open();
                }
                catch
                {
                    throw new Exception("Can not connect to database. Please check connect");
                }
                SqlDataReader reader = command.ExecuteReader();
                IList<HomeInfo> result = new List<HomeInfo>();
                while (reader.Read())
                {
                    StreetInfo street = new StreetInfo();
                    street.City= Convert.ToString(reader["city"]);
                    street.District = Convert.ToString(reader["District"]);
                    street.FullName= Convert.ToString(reader["fullname"]);
                    street.Ward= Convert.ToString(reader["ward"]);
                    HomeInfo home = new HomeInfo();
                    home.HomeNumber = Convert.ToString(reader["number"]);
                    home.Street = street;
                    home.Coordinate = new Coordinate(Convert.ToDouble(reader["xlocation"]), Convert.ToDouble(reader["ylocation"]));
                    result.Add(home);
                }
                return result;
            }
        }

        public HomeInfo GetAddress(NodeInfo node)
        {
            using (SqlConnection sqlConnect = new SqlConnection(GlobalConfig.ConnectionString))
            {
                SqlCommand command = new SqlCommand();
                command.CommandText = "sp_StreetDetail_NodeToAddress";
                command.CommandType = CommandType.StoredProcedure;
                command.Connection = sqlConnect;
                command.CommandTimeout = GlobalConfig.TimeOut;
                command.Parameters.AddWithValue("@xLocation",node.Location.Longitude);
                command.Parameters.AddWithValue("@yLocation", node.Location.Latitude);
                try
                {
                    sqlConnect.Open();
                }
                catch
                {
                    throw new Exception("Can not connect to database. Please check connect");
                }
                SqlDataReader reader = command.ExecuteReader();
                HomeInfo homeInfo = new HomeInfo();
                while (reader.Read())
                {
                    StreetInfo street = new StreetInfo();
                    street.District = Convert.ToString(reader["district"]);
                    street.FullName = Convert.ToString(reader["fullname"]);
                    street.Ward = Convert.ToString(reader["ward"]);
                    homeInfo.HomeNumber = Convert.ToString(reader["number"]);
                    homeInfo.Street = street;
                    
                }
                return homeInfo;

            }


        }

        public IList<StreetTraffic> GetStreetTrafficInArea(Coordinate start, Coordinate end)
        {
            using (SqlConnection sqlConnect = new SqlConnection(GlobalConfig.ConnectionString))
            {
                SqlCommand command = new SqlCommand();
                command.CommandText = "sp_StreetTraffic_GetStreetTrafficInArea";
                command.CommandType = CommandType.StoredProcedure;
                command.Connection = sqlConnect;
                command.CommandTimeout = GlobalConfig.TimeOut;
                command.Parameters.AddWithValue("@xLocationStart", start.Latitude);
                command.Parameters.AddWithValue("@yLocationStart", start.Longitude);
                command.Parameters.AddWithValue("@yLocationEnd", end.Longitude);
                command.Parameters.AddWithValue("@xLocationEnd", end.Latitude);
                try
                {
                    sqlConnect.Open();
                }
                catch
                {
                    throw new Exception("Can not connect to database. Please check connect");
                }
                SqlDataReader reader = command.ExecuteReader();
                IList<StreetTraffic> results = new List<StreetTraffic>();
                while (reader.Read())
                {
                    StreetTraffic streetTraffic = new StreetTraffic();
                    streetTraffic.Cause = Convert.ToString(reader["Cause"]);
                    streetTraffic.Direction = Convert.ToInt32(reader["Direction"]);
                    streetTraffic.Image = Convert.ToString(reader["Image"]);
                    streetTraffic.Level = Convert.ToInt32(reader["Level"]);
                    streetTraffic.Speed = Convert.ToInt32(reader["Speed"]);
                    streetTraffic.State = Convert.ToInt32(reader["State"]);
                    streetTraffic.UpdateTime = Convert.ToDateTime(reader["UpdateTime"]);
                    streetTraffic.BeginLocation = new Coordinate();
                    streetTraffic.BeginLocation.Latitude = Convert.ToDouble(reader["xBeginPoint"]);
                    streetTraffic.BeginLocation.Longitude = Convert.ToDouble(reader["yBeginPoint"]);
                    streetTraffic.EndLocation = new Coordinate();
                    streetTraffic.EndLocation.Latitude = Convert.ToDouble(reader["xEndPoint"]);
                    streetTraffic.EndLocation.Longitude = Convert.ToDouble(reader["yEndPoint"]);
                    results.Add(streetTraffic);
                }
                return results;
            }
        }

    }
}
